﻿Imports System.Data.OleDb

Partial Public Class AddGroup
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub btn_AddGroup_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_AddGroup.Click
        Dim conn = New OleDbConnection(ConfigurationManager.ConnectionStrings("PlacesConnectionString").ToString())

        Dim addGroupSQL As String = "INSERT INTO groups(GroupName,Description,UserName,GroupImage,GroupLocation) VALUES(@GroupName,@Description,@UserName,@GroupImage,@GroupLocation)"

        Dim fileLocationOnServer = Request.MapPath("Images/Group/" & upload_GroupImage.FileName)

        upload_GroupImage.SaveAs(fileLocationOnServer)

        Dim relativePath As String = "Images/Group/" & upload_GroupImage.FileName


        Dim dbcmd = New OleDbCommand(addGroupSQL, conn)

        dbcmd.Parameters.AddWithValue("@GroupName", tb_GroupTitle.Text)
        dbcmd.Parameters.AddWithValue("@Description", tb_GroupDescription.Text)
        dbcmd.Parameters.AddWithValue("@UserName", User.Identity.Name)
        Try
            dbcmd.Parameters.AddWithValue("@GroupImage", relativePath)
        Catch ex As Exception
        End Try
        dbcmd.Parameters.AddWithValue("@GroupLocation", tb_Location.Text)
        conn.Open()
        dbcmd.ExecuteNonQuery()
        addUserToGroup(getUserID(), getLastGroupAdded())
        Response.Redirect("groups.aspx")
    End Sub

    Sub addUserToGroup(ByVal UserName As String, ByVal groupId As Integer)
        Dim conn = New OleDbConnection(ConfigurationManager.ConnectionStrings("PlacesConnectionString").ToString())
        Dim sql As String = "INSERT INTO MemberGroupJunction (Group_fkid, Member) VALUES (@Group_fkid, @Member)"
        Dim cmd As New OleDbCommand(sql, conn)
        cmd.CommandType = CommandType.Text
        conn.Open()
        cmd.Parameters.AddWithValue("@Group_fkid", groupId)
        cmd.Parameters.AddWithValue("@Member", UserName)
        cmd.ExecuteNonQuery()
        conn.Close()
    End Sub

    Function getLastGroupAdded() As Integer
        Dim conn = New OleDbConnection(ConfigurationManager.ConnectionStrings("PlacesConnectionString").ToString())
        Dim sql As String = "SELECT TOP 1 ID FROM groups ORDER BY ID DESC"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        conn.Open()
        Dim dbread = cmd.ExecuteReader()
        dbread.Read()
        Dim returnVal = dbread("ID")
        conn.Close()
        Return returnVal
    End Function

    Function getUserID() As Integer
        Dim conn = New OleDbConnection(ConfigurationManager.ConnectionStrings("PlacesConnectionString").ToString())
        Dim loginName = User.Identity.Name
        Dim loginId As Integer
        Dim readLoginIdSQL As String = "SELECT UserId FROM aspnet_Users where UserName = @UserName"
        Dim cmd As New OleDbCommand(readLoginIdSQL, conn)
        conn.Open()
        cmd.Parameters.AddWithValue("@UserName", loginName)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        reader.Read()
        loginId = (reader("UserId"))
        conn.Close()
        Return loginId

    End Function

End Class